package com.ejia.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ejia.dao.BaseDao;
import com.ejia.pojo.car;
import com.ejia.pojo.house;
import com.ejia.pojo.user;

public class CarServices {

	public static int count() {
		String sql="select count(carno) from tb_car";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		try {
			if (rSet.next()) {
				return rSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public static ArrayList<car> find() {
		String sql="SELECT\n" + 
				"	carNo,\n" + 
				"	carNumber,\n" + 
				"	statu,\n" + 
				"	u.RealName,\n" + 
				"	u.id,\n" + 
				"	CONCAT( h1.hName, h.hName ) as mph\n" + 
				"\n" + 
				"FROM\n" + 
				"	tb_car\n" + 
				"	JOIN tb_user u\n" + 
				"	JOIN tb_house h\n" + 
				"	JOIN tb_house h1\n" + 
				"WHERE\n" + 
				"	tb_car.userId = u.Id \n" + 
				"	AND ( h.parentId = h1.hId ) \n" + 
				"	AND (\n" + 
				"	h.hId = u.hid \n" + 
				"	)";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<car> cars=new ArrayList<>();
		car car=null;
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				car=new car();
				user = new user();
				house= new house();
				car.setId(rSet.getInt("id"));
				car.setCarno(rSet.getString("carNo"));
				car.setCarnumber(rSet.getString("carNumber"));
				car.setStatu(rSet.getString("statu"));
				user.setRealname(rSet.getString("RealName"));
				house.setHname(rSet.getString("mph"));
				user.setHid(house);
				car.setUserid(user);
				cars.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cars;
	}
	
	
	public static ArrayList<car> find(String RealName) {
		String sql="SELECT\n" + 
				"	c.carNo AS carNo,\n" + 
				"	c.carNumber AS carNumber,\n" + 
				"	c.pic AS pic,\n" + 
				"	u.RealName AS RealName,\n" + 
				"	c.statu AS statu,\n" + 
				"	h.hName AS hName,\n" + 
				"	h1.hName AS area \n" + 
				"FROM\n" + 
				"	ejiadb.tb_car c\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"	JOIN ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1 \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			c.userId = u.Id \n" + 
				"			) \n" + 
				"	AND ( u.hid = h.hId ) \n" + 
				"	AND ( h.parentId = h1.hId )\n" + 
				"	AND(u.RealName=?))";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,RealName);
		ArrayList<car> cars=new ArrayList<>();
		car car=null;
		user user=null;
		try {
			while (rSet.next()) {
				car=new car();
				user = new user();
				car.setCarno(rSet.getString("carNo"));
				car.setCarnumber(rSet.getString("carNumber"));
				car.setStatu(rSet.getString("statu"));
				user.setRealname(rSet.getString("RealName"));
				car.setUserid(user);
				cars.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cars;
	}
	
	
	
	
	public static boolean update(String newcarno,String carnumber,String statu,String oldcarno) {
		String sql="update tb_car set carno=?,carnumber=?,statu=? where carno=?";
		return new BaseDao().execute(sql,newcarno,carnumber,statu,oldcarno);
	}
	
	public static boolean update1(String carnumber,int userid) {
		String sql="update tb_car set carnumber=? where userid=?";
		return new BaseDao().execute(sql,carnumber,userid);
	}
	
	
	
	
	public static boolean delete(String id) {
		String sql="delete from tb_car where carno=?";
		return new BaseDao().execute(sql,id);
	}
	
	
	
	public static boolean add(int userid,String carno,String carnumber,String statu) {
		String sql="insert into tb_car(userid,carno,carnumber,statu) values(?,?,?,?)";
		return new BaseDao().execute(sql, userid,carno,carnumber,statu);
	}
	
	
	public static ArrayList<car> findinfo(String realname) {
		String sql="SELECT\n" + 
				"	*\n,u.remark AS urk " + 
				"FROM\n" + 
				"	ejiadb.tb_car c\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"	JOIN ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1 \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			c.userId = u.Id \n" + 
				"			) \n" + 
				"	AND ( u.hid = h.hId ) \n" + 
				"	AND ( h.parentId = h1.hId )\n" + 
				"	and u.RealName=?)";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql, realname);
		ArrayList<car> cars= new ArrayList<>();
		car car=null;
		user user=null;
		house house= null;
		try {
			while (rSet.next()) {
				car = new car();
				user = new user();
				house = new house();
				user.setRealname(rSet.getString("Realname"));
				user.setSex(rSet.getString("Sex"));
				user.setUid(rSet.getString("Uid"));
				user.setPwd(rSet.getString("Pwd"));
				user.setPhone(rSet.getString("Phone"));
				user.setEmail(rSet.getString("Email"));
				user.setBirthday(rSet.getString("Birthday"));
				house.setHid(rSet.getInt("hid"));
				house.setHname(rSet.getString("hname"));
				house.setRemark(rSet.getString("urk"));
				house.setAddress(rSet.getString("address"));
				user.setId(rSet.getInt("userid"));
				car.setCarnumber(rSet.getString("Carnumber"));
				user.setHid(house);
				car.setUserid(user);
				cars.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return cars;
	}
	
	
	
}
